package com.wind.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import com.wind.model.Product;

/**
 * Dao类
 * @author www.java1234.com
 *
 */
public class ProductDao {
	
	/*public static void main(String[] args) {
		
		for(int i=0;i<100;i++){
			Product p=new Product();
			p.setProductId("pro"+i);
			p.setListPrice(i);
			p.setUnitCost(i+1);
			p.setAttr1("attr"+i);
			p.setP("P");
			add(p);
		}
	}*/
	
	

	public JSONArray query(int start,int limit,String sort,String order){
		JSONArray jsonArray=new JSONArray();
		try{
			Connection conn=getConnection();
			StringBuffer sql=new StringBuffer("select * from t_product ");
			if(!"".equals(sort)&&sort!=null){
				sql.append(" order by "+sort+" "+order);
			}
			sql.append(" limit ?,? ");
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setInt(1, start);
			ps.setInt(2, limit);
			
			
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				JSONObject obj=new JSONObject();
				obj.put("itemid", rs.getInt("itemid"));
				obj.put("productid", rs.getString("productid"));
				obj.put("listprice", rs.getInt("listprice"));
				obj.put("unitcost", rs.getInt("unitcost"));
				obj.put("attr1", rs.getString("attr1"));
				obj.put("status", rs.getString("status"));
				jsonArray.add(obj);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return jsonArray;
	}
	
	/**
	 * 添加记录
	 * @param p 
	 */
	public  void add(Product p) {
		
		try {
			Connection conn = getConnection();	
			String sql = "insert into t_product(itemId,productId, listPrice, unitCost,attr1, p)"
					+ " VALUES (null,?, ?, ?, ?, ?)";	
			PreparedStatement ps=conn.prepareStatement(sql);	
			ps.setString(1, p.getProductId());
			ps.setInt(2, p.getListPrice());
			ps.setInt(3, p.getUnitCost());
			ps.setString(4, p.getAttr1());
			ps.setString(5, p.getP());
			ps.executeUpdate();
			conn.close();	
		} catch (SQLException e) {
			System.out.println("插入数据失败" + e.getMessage());
		}
	}

	
	/* 获取数据库连接的函数*/
	public static Connection getConnection() {
		Connection con = null;	
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/test", "root", "123456");
			
		} catch (Exception e) {
			System.out.println("数据库连接失败" + e.getMessage());
		}
		return con;	
	}
	
}
